Oracle 的 DBMS_SCN 修正以及 SCN 的 auto-rollover 新特性
在 Oracle 11.2.0.2 之后，随着一系列 SCN 耗尽问题的出现，很多补丁涌现出来，一个新的 Package 增加进来。
这个 Package 就是 DBMS_SCN。
如果你的数据库中存在这个 Package，也就意味着你已经安装具备了关于 DB Link 的修正补丁。
以下是这个包的主要函数过程以及说明，这个内容来自 Oracle 11.2.0.4 版本平台：
Rem
Rem $Header: rdbms/admin/dbmsscnc.sql /st_rdbms_11.2.0/1 2013/04/18 23:05:40 vgokhale Exp $
Rem
Rem dbmsscn.sql
Rem
Rem Copyright (c) 2012, 2013, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem dbmsscnc.sql - dbms_scn package definition
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem mtiwary 05/26/12 - Declarations and definitions related to DBMS_SCN
Rem package.
Rem mtiwary 05/26/12 - Created
Rem
Rem
Rem BEGIN SQL_FILE_METADATA
Rem SQL_SOURCE_FILE: rdbms/admin/dbmsscn.sql
Rem SQL_SHIPPED_FILE:
Rem SQL_PHASE:
Rem SQL_STARTUP_MODE: NORMAL
Rem SQL_IGNORABLE_ERRORS: NONE
Rem SQL_CALLING_FILE:
Rem END SQL_FILE_METADATA
SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
CREATE OR REPLACE LIBRARY DBMS_SCN_LIB TRUSTED AS STATIC;
/
CREATE OR REPLACE PACKAGE DBMS_SCN AUTHID CURRENT_USER IS
DBMS_SCN_API_MAJOR_VERSION CONSTANT NUMBER := 1;
DBMS_SCN_API_MINOR_VERSION CONSTANT NUMBER := 0;
PROCEDURE GetCurrentSCNParams(
rsl OUT number,
headroom_in_scn OUT number,
headroom_in_sec OUT number,
cur_scn_compat OUT number,
max_scn_compat OUT number);
-- Currently no exceptions are thrown.
-- rsl - Reasonable SCN Limit as of 'now'
-- headroom_in_scn - Difference between current SCN and RSL
-- headroom_in_sec - number of seconds it would take to reach RSL
-- assuming a constant SCN consumption rate associated
-- with current SCN compatibility level
-- cur_scn_compat - current value of SCN compatibility
-- max_scn_compat - max value of SCN compatibility this database
-- understands
FUNCTION GetSCNParamsByCompat(
compat IN number,
rsl OUT number,
headroom_in_scn OUT number,
headroom_in_sec OUT number
) RETURN boolean;
-- compat -- SCN compatibility value
-- rsl -- Reasonable SCN Limit
-- headroom_in_scn -- Difference between current SCN and RSL
-- headroom_in_sec -- number of seconds it would take to reach RSL
-- assuming a constant SCN consumption rate associated
-- with specified database SCN compatibility
--
-- Returns False if 'compat' parameter value is invalid, and OUT parameters
-- are not updated.
PROCEDURE GetSCNAutoRolloverParams(
effective_auto_rollover_ts OUT DATE,
target_compat OUT number,
is_enabled OUT boolean);
-- effective_auto_rollover_ts - timestamp at which rollover becomes
-- effective
-- target_compat - SCN compatibility value this database
-- will move to, as a result of
-- auto-rollover
-- is_enabled - TRUE if auto-rollover feature is
-- currently enabled
PROCEDURE EnableAutoRollover;
PROCEDURE DisableAutoRollover;
END DBMS_SCN;
/
这里就可以看到 auto-rollover 的自动 SCN 兼容性终止时间，也就是说，在不同的兼容性设置中，SCN 的算法不同，但是内置了天然的算法过期时间。
在此之后，可以通过命令修改数据库的 SCN 兼容性算法：
SQL> ALTER DATABASE SET SCN COMPATIBILITY 2;
Database altered.
从高级别向低级别修改，需要数据库在 Mount 状态：
SQL> ALTER DATABASE SET SCN COMPATIBILITY 2;
ALTER DATABASE SET SCN COMPATIBILITY 2
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any instance
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 4609830912 bytes
Fixed Size 2260888 bytes
Variable Size 989855848 bytes
Database Buffers 3607101440 bytes
Redo Buffers 10612736 bytes
Database mounted.
SQL> ALTER DATABASE SET SCN COMPATIBILITY 2;
Database altered.
SQL> alter database open;
Database altered.
这是一个非常重要的变化。
云和恩墨